namespace CISLib
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageCAT
    {
        public static SqlDataReader GetCAT()
        {
            string strSQL = "Select * from TB_CATGroup";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetCAT(Guid areaId)
        {
            string strSQL = "Select * from TB_CATGroup where AreaId=@AreaId ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static Cat[] GetCATAryViaId(Guid userId)
        {
            SqlDataReader MyReader = null;
            ArrayList catList = new ArrayList();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(userId, "UserId", SqlDbType.UniqueIdentifier);
            string strSQL = "Select TB_UserCAT.CATId from TB_UserCAT left outer join TB_CATGroup on TB_UserCAT.CATId = TB_CATGroup.CATGroupId  where UserId = @UserId order by TB_CATGroup.CATGroupName";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            while (MyReader.Read())
            {
                try
                {
                    Cat cat = new Cat();
                    cat = GetCATViaId(MyReader.GetGuid(0));
                    catList.Add(cat);
                    continue;
                }
                catch
                {
                    continue;
                }
            }
            MyDB.DBClose();
            return (Cat[]) catList.ToArray(typeof(Cat));
        }

        public static Cat GetCATViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            Cat cat = new Cat();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CATGroup where CATGroupId = @CATGroupId";
            MyDB.AddParameters(id, "CATGroupId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                cat.CatId = MyReader.GetGuid(0);
                cat.CatName = MyReader.GetString(1);
                cat.CatDescript = MyReader.GetString(2);
            }
            MyDB.DBClose();
            return cat;
        }

        public static void InsertNewCAT(string name, string descr, Guid areaId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "CATGroupName", SqlDbType.VarChar);
            MyDB.AddParameters(descr, "CATGroupDescr", SqlDbType.VarChar);
            MyDB.AddParameters(id, "CATGroupId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_CATGroup (CATGroupName,CATGroupDescr,CATGroupId,AreaId) values (@CATGroupName,@CATGroupDescr,@CATGroupId,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistCATInOtherTable(Guid catId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_UserCAT where CATId = @CATId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            strSQL = "select * from TB_CustomerBasicInfo where CATId = @CATId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            MyDB.DBOpen();
            MyDB.AddParameters(catId, "CATId", SqlDbType.UniqueIdentifier);
            strSQL = "select * from TB_CompanyInfo where CATId = @CATId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            MyDB.ClearParameters();
            return false;
        }

        public static bool IsExistSameCATName(string name, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "CATGroupName", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CATGroup where CATGroupName = @CATGroupName and AreaId=@AreaId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveCATViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "CATGroupId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CATGroup where CATGroupId = @CATGroupId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateCAT(string name, string descr, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "CATGroupName", SqlDbType.VarChar);
            MyDB.AddParameters(descr, "CATGroupDescr", SqlDbType.VarChar);
            MyDB.AddParameters(id, "CATGroupId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CATGroup set CATGroupName = @CATGroupName,CATGroupDescr = @CATGroupDescr where CATGroupId = @CATGroupId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

